package score;

import pojo.*;
import resultHandler.MyColumnListHandler;
import resultHandler.MyTableListHandler;

import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

public class DbComparator {
    // key=表名，防止重复添加表的
    private static Map<String, TableDiff> tableDiffsMap = new TreeMap<>();
    // key=表名.字段名  防止重复添加列的
    private static Map<String, ColumnDiff> columnDiffsMap = new TreeMap<>();

    public static void main(String[] args) throws SQLException, ClassNotFoundException, IOException {
        Db db1 = new Db("cd-fy-2401", "jdbc:mysql://localhost:3306/cd-fy-2401", "root", "1234");
        Db db2 = new Db("stu_record_manager_sys", "jdbc:mysql://localhost:3306/stu_record_manager_sys", "root", "1234");
        CompareResult compareResult = new DbComparator().compare(db1, db2);
        // 写入文件
        String string = compareResult.toString();
        writeToFile(string);
    }

    /**
     * 获取数据库的链接对象
     *
     * @param db
     * @return
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public static Connection getConnection(Db db) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection connection = DriverManager.getConnection(db.getUrl(), db.getUsername(), db.getPassword());
        return connection;
    }

    /**
     * 比较两个数据库的表和列
     *
     * @param db1
     * @param db2
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public CompareResult compare(Db db1, Db db2) throws SQLException, ClassNotFoundException {
        CompareResult compareResult = new CompareResult();

        List<TableDiff> tableDiffs = new ArrayList<>();
        List<ColumnDiff> columnDiffs = new ArrayList<>();

        compareResult.setTableDiffs(tableDiffs);
        compareResult.setColumnDiffs(columnDiffs);

        Connection connection1 = getConnection(db1);
        Connection connection2 = getConnection(db2);
        String sql = "SHOW TABLES";
        ResultSet tableResultSet1 = connection1.prepareStatement(sql).executeQuery();
        ResultSet tableResultSet2 = connection2.prepareStatement(sql).executeQuery();
        //两个数据库的表
        List<String> tableList1 = new MyTableListHandler().handle(tableResultSet1);
        List<String> tableList2 = new MyTableListHandler().handle(tableResultSet2);

        //遍历查询db1中的表
        for (int i = 0; i < tableList1.size(); i++) {
            String tableName = tableList1.get(i);
            TableDiff tableDiff1 = new TableDiff();
            tableDiff1.setName(tableName);
            //db1中肯定没有重复的数据，直接添加就行
            tableDiffsMap.put(tableName, tableDiff1);
            tableDiffs.add(tableDiff1);
            if (!tableList2.contains(tableName)) {
                //db1有，db2没有
                tableDiff1.setDiff(3);
                continue;
            }
            //走到这说明该表在两个数据库中都存在
            //查询当前表的所有列
            String queryColumnSql = "SHOW COLUMNS FROM " + tableName;
            ResultSet columnResultSet1 = connection1.prepareStatement(queryColumnSql).executeQuery();
            ResultSet columnResultSet2 = connection2.prepareStatement(queryColumnSql).executeQuery();
            List<Column> columnList1 = new MyColumnListHandler().handle(columnResultSet1);
            List<Column> columnList2 = new MyColumnListHandler().handle(columnResultSet2);
            //判断两个数据库中的这些列是否完全相同
            if (columnIsSame(columnList1, columnList2, tableName, columnDiffs)) {
                //表都有，且列完全相同
                tableDiff1.setDiff(1);
                continue;
            }
            //走到这说明表都有，但是列存在不同
            tableDiff1.setDiff(2);
        }
        //遍历查询db2中的表
        for (int i = 0; i < tableList2.size(); i++) {
            String tableName = tableList2.get(i);
            TableDiff tableDiff2 = new TableDiff();
            tableDiff2.setName(tableName);
            //db1已经记录过一次了，这里可能会重复，所以防止重复记录的
            if (tableDiffsMap.containsKey(tableName)) {
                continue;
            }
            tableDiffs.add(tableDiff2);
            tableDiffsMap.put(tableName, tableDiff2);
            if (!tableList1.contains(tableName)) {
                //db2有，db1没有
                tableDiff2.setDiff(4);
            }
            //查询当前表的所有列
            String queryColumnSql = "SHOW COLUMNS FROM " + tableName;
            ResultSet columnResultSet1 = connection1.prepareStatement(queryColumnSql).executeQuery();
            ResultSet columnResultSet2 = connection2.prepareStatement(queryColumnSql).executeQuery();
            List<Column> columnList1 = new MyColumnListHandler().handle(columnResultSet1);
            List<Column> columnList2 = new MyColumnListHandler().handle(columnResultSet2);
            //判断两个数据库中的这些列是否完全相同
            if (columnIsSame(columnList1, columnList2, tableName, columnDiffs)) {
                //表都有，且列完全相同
                tableDiff2.setDiff(1);
                continue;
            }
            //走到这说明表都有，但是列存在不同
            tableDiff2.setDiff(2);
        }
        return compareResult;
    }

    /**
     * 判断两个表的列是否完全相同，并且在这里面添加ColumnDiff集合
     *
     * @param columnList1
     * @param columnList2
     * @param tableName
     * @param columnDiffs
     * @return
     */
    private boolean columnIsSame(List<Column> columnList1, List<Column> columnList2, String tableName, List<ColumnDiff> columnDiffs) {
        boolean isSame = true;
        // 遍历db1的表的列
        for (int i = 0; i < columnList1.size(); i++) {
            Column column1 = columnList1.get(i);
            String key = tableName + "." + column1.getField();
            ColumnDiff columnDiff = null;
            // 判断是否已经添加过
            if (!columnDiffsMap.containsKey(key)) {
                // 没有添加过则新建，并且添加进容器
                columnDiff = new ColumnDiff();
                columnDiff.setTable(tableName);
                columnDiff.setName(column1.getField());
                columnDiffsMap.put(key, columnDiff);
                columnDiffs.add(columnDiff);
            } else {
                columnDiff = columnDiffsMap.get(key);
            }
            //在db2中找对应的列
            for (int j = 0; j < columnList2.size(); j++) {
                Column column2 = columnList2.get(j);
                if (column1.getField().equals(column2.getField())) {
                    //找到了相同名字的列，对比它的各个属性一样不
                    if (!strIsSame(column1.getType(), column2.getType()) ||
                            !strIsSame(column1.getIsNull(), column2.getIsNull()) ||
                            !strIsSame(column1.getKey(), column2.getKey()) ||
                            !strIsSame(column1.getDefaultValue(), column2.getDefaultValue()) ||
                            !strIsSame(column1.getExtra(), column2.getExtra())) {
                        // 走到这里说明字段都有但是存在不同
                        isSame = false;
                        columnDiff.setDiff(2);
                        break;
                    }
                    // 找到了相同名字的列，说明db1和db2都有，并且走到这里说明列完全相同
                    isSame = true;
                    columnDiff.setDiff(1);
                    break;
                }
                // 遍历完db2的列，如果没找到相同名字的列，则说明db1有，db2没有
                if (j == columnList2.size() - 1) {
                    isSame = false;
                    columnDiff.setDiff(3);
                    break;
                }
            }
        }
        // 遍历db2的表的列
        for (int i = 0; i < columnList2.size(); i++) {
            Column column2 = columnList2.get(i);
            String key = tableName + "." + column2.getField();
            ColumnDiff columnDiff = null;
            // 判断是否已经添加过
            if (!columnDiffsMap.containsKey(key)) {
                // 没有添加过则新建，并且添加进容器
                columnDiff = new ColumnDiff();
                columnDiff.setTable(tableName);
                columnDiff.setName(column2.getField());
                columnDiffsMap.put(key, columnDiff);
                columnDiffs.add(columnDiff);
            } else {
                columnDiff = columnDiffsMap.get(key);
            }
            //在db1中找对应的列
            for (int j = 0; j < columnList1.size(); j++) {
                Column column1 = columnList1.get(j);
                if (column2.getField().equals(column1.getField())) {
                    //找到了相同名字的列，对比它的各个属性一样不
                    if (!strIsSame(column1.getType(), column2.getType()) ||
                            !strIsSame(column1.getIsNull(), column2.getIsNull()) ||
                            !strIsSame(column1.getKey(), column2.getKey()) ||
                            !strIsSame(column1.getDefaultValue(), column2.getDefaultValue()) ||
                            !strIsSame(column1.getExtra(), column2.getExtra())) {
                        // 走到这里说明字段都有但是存在不同
                        isSame = false;
                        columnDiff.setDiff(2);
                        break;
                    }
                    // 找到了相同名字的列，说明db1和db2都有，并且走到这里说明列完全相同
                    isSame = true;
                    columnDiff.setDiff(1);
                    break;
                }
                // 遍历完db1的列，如果没找到相同名字的列，则说明db2有，db1没有
                if (j == columnList1.size() - 1) {
                    isSame = false;
                    columnDiff.setDiff(4);
                    break;
                }
            }
        }
        return isSame;
    }

    /**
     * 判断两个字段是否相同
     *
     * @param str1
     * @param str2
     * @return
     */
    private boolean strIsSame(String str1, String str2) {
        if (str1 == null && str2 == null) {
            return true;
        }
        if (str1 == null || str2 == null) {
            return false;
        }
        return str1.equals(str2);
    }

    /**
     * 通过io写入到文件中
     */
    private static void writeToFile(String string) throws IOException {
        int count = string.length();
        FileWriter fileWriter = new FileWriter("backend3.txt");
        for (int i = 0; i < count / 130; i++) {
            String writeStr = string.substring(0, 130);
            string = string.substring(130);
            fileWriter.write(writeStr);
            fileWriter.write("\n");
        }
        fileWriter.write(string);
        fileWriter.write("\n");
        fileWriter.flush();
        fileWriter.close();
    }
}